package com.fjnu.dao.impl;

import com.fjnu.dao.UserDao;
import com.fjnu.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

@Repository
@Scope("singleton")
public class UserDaoImpl implements UserDao {
    /**
     * 注入 connection Bean,利用connection bean来完成对数据库的操作
     */
    @Autowired
    private Connection connection;

    @Override
    public int insert(User user) {
        String sql = "INSERT INTO user (user_id, username, password, salt, nickname, email, phone, sex, age, img, status, create_time, update_time, last_login_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, user.getUserId());
            ps.setString(2, user.getUsername());
            ps.setString(3, user.getPassword());
            ps.setString(4, user.getSalt());
            ps.setString(5, user.getNickname());
            ps.setString(6, user.getEmail());
            ps.setString(7, user.getPhone());
            ps.setObject(8, user.getSex());
            ps.setObject(9, user.getAge());
            ps.setString(10, user.getImg());
            ps.setObject(11, user.getStatus());
            ps.setObject(12, user.getCreateTime());
            ps.setObject(13, user.getUpdateTime());
            ps.setObject(14, user.getLastLoginTime());

            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int delete(String userId) {
        String sql = "DELETE FROM user WHERE user_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, userId);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int updateById(String userId, User newUser) {
        String sql = "UPDATE user SET nickname = ?, email = ?, phone = ?, status = ?, update_time = ? WHERE user_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, newUser.getNickname());
            ps.setString(2, newUser.getEmail());
            ps.setString(3, newUser.getPhone());
            ps.setObject(4, newUser.getStatus());
            ps.setObject(5, new Date()); // update time
            ps.setString(6, userId);

            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int updatePasswordById(String userId, String oldPassword, String newPassword) {
        // 先验证旧密码是否正确
        User user = selectById(userId);
        if (user == null || !user.getPassword().equals(oldPassword)) {
            return 0; // 旧密码不正确
        }

        String sql = "UPDATE user SET password = ?, update_time = ? WHERE user_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, newPassword);
            ps.setObject(2, new Date()); // update time
            ps.setString(3, userId);

            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public User selectById(String userId) {
        String sql = "SELECT * FROM user WHERE user_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, userId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setUserId(rs.getString("user_id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setSalt(rs.getString("salt"));
                user.setNickname(rs.getString("nickname"));
                user.setEmail(rs.getString("email"));
                user.setPhone(rs.getString("phone"));
                user.setSex(rs.getObject("sex", Long.class));
                user.setAge(rs.getObject("age", Long.class));
                user.setImg(rs.getString("img"));
                user.setStatus(rs.getObject("status", Integer.class));
                user.setCreateTime(rs.getObject("create_time", Date.class));
                user.setUpdateTime(rs.getObject("update_time", Date.class));
                user.setLastLoginTime(rs.getObject("last_login_time", Date.class));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public User selectByUsernameAndPassword(String username, String password) {
        String sql = "SELECT * FROM user WHERE username = ? AND password = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setUserId(rs.getString("user_id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setSalt(rs.getString("salt"));
                user.setNickname(rs.getString("nickname"));
                user.setEmail(rs.getString("email"));
                user.setPhone(rs.getString("phone"));
                user.setSex(rs.getObject("sex", Long.class));
                user.setAge(rs.getObject("age", Long.class));
                user.setImg(rs.getString("img"));
                user.setStatus(rs.getObject("status", Integer.class));
                user.setCreateTime(rs.getObject("create_time", Date.class));
                user.setUpdateTime(rs.getObject("update_time", Date.class));
                user.setLastLoginTime(rs.getObject("last_login_time", Date.class));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int selectCount() {
        String sql = "SELECT COUNT(*) FROM user";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}